package com.bxl;

import java.sql.*;
import java.util.Date;

public class NewsJdbc1 {

    public static void main(String[] args) throws SQLException {
        NewsJdbc1 newsJdbc1 = new NewsJdbc1();

        System.out.println( new Timestamp(new Date().getTime()));

        // newsJdbc1.selectAll();

        //newsJdbc1.selectById(2,"国际");

      /* int count= newsJdbc1.updateById("gug",2);
        if(count>0){
            System.out.println("修改成功");
        }*/
        /*int count= newsJdbc1.deleteById(4);

        if(count>0){
            System.out.println("删除成功");
        }*/


        int count= newsJdbc1.insertNews(5,6,"123","432","123",new Date());
        if(count>0){
            System.out.println("新增成功");
        }

    }

    //添加数据
    public int insertNews(int id,int cotyid,String title,
                          String sumay,String content,Date date) throws SQLException{
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        int row=0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://127.0.0.1:3306/kgcnews";
            String username="root";
            String password="123456";
            connection=DriverManager.getConnection(url,username,password);

            String sql="INSERT INTO news_detail(id,`categoryId`,`title`,`summary`,`content`,`createDate`)VALUES(?,?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setInt(2,cotyid);
            preparedStatement.setString(3,title);
            preparedStatement.setString(4,sumay);
            preparedStatement.setString(5,content);
            preparedStatement.setTimestamp(6,new Timestamp(date.getTime()));
            row= preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            preparedStatement.close();
            connection.close();
        }

        return row;
    }



    //根据id进行删除
    public  int deleteById(int id) throws SQLException{
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        int row=0;
          try {
              Class.forName("com.mysql.jdbc.Driver");
              String url="jdbc:mysql://127.0.0.1:3306/kgcnews";
              String username="root";
              String password="123456";
              connection=DriverManager.getConnection(url,username,password);

              String sql="DELETE FROM news_detail WHERE id=?";
              preparedStatement=connection.prepareStatement(sql);
              preparedStatement.setInt(1,id);
              row= preparedStatement.executeUpdate();

          }catch (Exception e){
              e.printStackTrace();
          }finally {
              preparedStatement.close();
              connection.close();
          }
          return row;
    }


    //根据id进行修改
    public int updateById(String name,int id) throws  SQLException{
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        int rows=0;
       try {
           Class.forName("com.mysql.jdbc.Driver");
           String url="jdbc:mysql://127.0.0.1:3306/kgcnews";
           String username="root";
           String password="123456";
           connection=DriverManager.getConnection(url,username,password);
           String sql="UPDATE news_category SET name=? WHERE id=?";
           preparedStatement=connection.prepareStatement(sql);
           preparedStatement.setString(1,name);
           preparedStatement.setInt(2,id);
           rows=preparedStatement.executeUpdate();



       }catch (Exception e){
           e.printStackTrace();
       }finally {
           preparedStatement.close();
           connection.close();
       }
        return rows;
    }



    //根据id查询信息
    public void selectById(int id,String name) throws SQLException{
        Connection connection=null;
        Statement statement=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
          try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://127.0.0.1:3306/kgcnews";
            String username="root";
            String password="123456";
            connection= DriverManager.getConnection(url,username,password);
            String sql="SELECT * FROM news_category WHERE id=? and name=?";
            //statement=connection.createStatement();//不带条件的查询
              //带条件的查询
             preparedStatement= connection.prepareStatement(sql);

             preparedStatement.setInt(1,id);
             preparedStatement.setString(2,name);

             resultSet=preparedStatement.executeQuery();
             while (resultSet.next()){
                 int ids=resultSet.getInt("id");
                 String name1=resultSet.getString("name");
                 System.out.println("输出："+ids+"-----"+name1);
             }
          }catch (Exception e){
                e.printStackTrace();
          }finally {
              resultSet.close();
              preparedStatement.close();
              connection.close();
          }
    }


    //查询所有信息
    public  void selectAll()  throws SQLException {
        Connection connection=null;
        Statement statement =null;
        ResultSet res=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/kgcnews";
            String username ="root";
            String password="123456";
            connection=  DriverManager.getConnection(url,username,password);
            statement = connection.createStatement();
            String sql="SELECT * FROM news_category";
            res=statement.executeQuery(sql);
            while (res.next()){
                int id=res.getInt("id");
                String name=res.getString("name");
                System.out.println("输出："+id+"-----"+name);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            res.close();
            statement.close();
            connection.close();
        }

    }
}
